const mysql = require('../db/mysql57');

const managerDao = {
    // 获取副卡密详情 - 无需判断父卡密是谁
    getSubCardInfoById(cardId) {
        let sql = `select
                id, card, name, url, count, maxCount, isEnable,
                DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
            from User
            where id = ? and isDel = ? and userTypeId = (
                    select id from UserType where name = ?
                )`;
        let data = [ cardId, 'false', '副卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 懒加载获取副卡密列表
    getAllSubCardLazyLoad(uid, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    u.id as id,
                    u.card as card,
                    u.name as name,
                    u.count as count,
                    u.maxCount as maxCount,
                    u.isEnable as isEnable,
                    (
                        select count(*) from User where parentId = u.id and isDel = ? and userTypeId = (
                            select id from UserType where name = ? LIMIT 1
                        )
                    ) as userCardCount,
                    DATE_FORMAT(u.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u where u.parentId = ? and u.userTypeId = (
                    select id from UserType where name = ? LIMIT 1
                ) and u.isDel = ? and u.id <= ? order by u.id desc LIMIT ?`;
            data = [ 'false', '用户卡密用户', uid, '副卡密用户', 'false', cursor, limit + 1 ];
        } else {
            sql = `select
                    u.id as id,
                    u.card as card,
                    u.name as name,
                    u.count as count,
                    u.maxCount as maxCount,
                    u.isEnable as isEnable,
                    (
                        select count(*) from User where parentId = u.id and isDel = ? and userTypeId = (
                            select id from UserType where name = ? LIMIT 1
                        )
                    ) as userCardCount,
                    DATE_FORMAT(u.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u where u.parentId = ? and u.userTypeId = (
                    select id from UserType where name = ? LIMIT 1
                ) and u.isDel = ? order by u.id desc LIMIT ?`;
            data = [ 'false', '用户卡密用户', uid, '副卡密用户', 'false', limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    // 直接获取所有副卡密列表
    getAllSubCard(uid) {
        let sql = `select
                    u.id as id,
                    u.card as card,
                    u.name as name,
                    u.count as count,
                    u.maxCount as maxCount,
                    u.isEnable as isEnable,
                    (
                        select count(*) from User where parentId = u.id and isDel = ? and userTypeId = (
                            select id from UserType where name = ? LIMIT 1
                        )
                    ) as userCardCount,
                    DATE_FORMAT(u.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u where u.parentId = ? and u.userTypeId = (
                    select id from UserType where name = ? LIMIT 1
                ) and u.isDel = ? order by u.id desc`;
        let data = [ 'false', '用户卡密用户', uid, '副卡密用户', 'false' ];
        return mysql.sqlExec(sql, data);
    },
    // 删除副卡密
    delSubCard(uid, cardId) {
        let sql = `update User set isDel = ? where (id = ? and parentId = ? and userTypeId = (
                select id from UserType where name = ?
            )) or (parentId = ? and userTypeId = (
                select id from UserType where name = ?
            ))`;
        let data = [ 'true', cardId, uid, '副卡密用户', cardId, '用户卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 修改副卡密信息
    updateSubCard(cardId, name, url) {
        let sql = `update User set name = ?, url = ? where id = ? and isDel = ? and userTypeId = (
                select id from UserType where name = ?
            )`;
        let data = [ name, url, cardId, 'false', '副卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 获取副卡密详情
    getSubCardInfo(uid, cardId) {
        let sql = `select
                id, card, name, url, count, maxCount, isEnable,
                DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
            from User
            where id = ? and isDel = ? and parentId = ? and
                userTypeId = (
                    select id from UserType where name = ?
                )`;
        let data = [ cardId, 'false', uid, '副卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 获取当前副卡密下的所有用户卡密信息
    getAllUserCard(uid) {
        let sql = `select
                id, card, name, url, count, maxCount, isEnable,
                DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
            from User where parentId = ? and isDel = ? and userTypeId = (
                select id from UserType where name = ? LIMIT 1
            ) order by id desc`;
        let data = [ uid, 'false', '用户卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 分页获取当前副卡密下的所有用户卡密信息
    getAllUserCardByPage(uid, page, size) {
        let c = (page - 1) * size;
        let sql = `select
                id, card, name, url, count, maxCount, isEnable,
                DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
            from User where parentId = ? and isDel = ? and userTypeId = (
                select id from UserType where name = ?
            ) LIMIT ?, ?`;
        let data = [ uid, 'false', '用户卡密用户', c, size ];
        return mysql.sqlExec(sql, data);
    },
    // 懒加载获取当前副卡密下的所有用户卡密信息
    getAllUserCardLazyLoad(uid, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where parentId = ? and
                    userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and
                    isDel = ? and
                    id <= ? order by id desc LIMIT ?`;
            data = [ uid, '用户卡密用户', 'false', cursor, limit + 1 ];
        } else {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where parentId = ? and
                    userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and
                    isDel = ? order by id desc LIMIT ?`;
            data = [ uid, '用户卡密用户', 'false', limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    // 搜索副卡密
    searchSubCard(uid, card) {
        let sql = `select
                id, card, name, url, count, maxCount, isEnable,
                DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
            from User
            where card = ? and isDel = ? and parentId = ? and userTypeId = (
                select id from UserType where name = ? LIMIT 1
            )`;
        let data = [ card, 'false', uid, '副卡密用户' ];
        return mysql.sqlExec(sql, data);
    },
    // 搜索用户卡密
    searchUserCard(uid, userType, card) {
        let sql;
        let data;
        if (userType == '主卡密用户') {
            sql = `select
                    u1.id as id,
                    u2.id as subCardId,
                    u1.card as card,
                    u1.name as name,
                    u2.name as subCardName,
                    u1.url as url,
                    u1.count as count,
                    u1.maxCount as maxCount,
                    u1.isEnable as isEnable,
                    u1.isDel as isDel,
                    DATE_FORMAT(u1.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u1.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u1
                left join User as u2 on u1.parentId = u2.id
                where u1.card = ? and u1.userTypeId = (
                    select id from UserType where name = ? LIMIT 1
                )`;
            data = [ card, '用户卡密用户' ];
        } else if (userType == '副卡密用户') {
            sql = `select
                    u1.id as id,
                    u2.id as subCardId,
                    u1.card as card,
                    u1.name as name,
                    u2.name as subCardName,
                    u1.url as url,
                    u1.count as count,
                    u1.maxCount as maxCount,
                    u1.isEnable as isEnable,
                    DATE_FORMAT(u1.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u1.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u1
                left join User as u2 on u1.parentId = u2.id
                where u1.card = ? and u1.isDel = ? and u1.parentId = ? and u1.userTypeId = (
                    select id from UserType where name = ? LIMIT 1
                )`;
            data = [ card, 'false', uid, '用户卡密用户' ];
        } else {
            return [];
        }
        return mysql.sqlExec(sql, data);
    },
    // 获取用户卡密详情
    getUserCardInfo(uid, userType, id) {
        let sql;
        let data;
        if (userType == '主卡密用户') {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable, isDel,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where id = ? and userTypeId = (
                    select id from UserType where name = ? limit 1
                ) limit 1`;
            data = [ id, '用户卡密用户' ];
        } else if (userType == '用户卡密用户') {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where id = ? and isDel = ? and parentId = ? and userTypeId = (
                    select id from UserType where name = ? limit 1
                ) limit 1`;
            data = [ id, 'false', uid, '用户卡密用户' ];
        } else {
            return [];
        }
        return mysql.sqlExec(sql, data);
    },
    // 懒加载获取某个副卡密下的用户卡密列表
    getAllUserCardBySubCardLazyLoad(cardId, cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where parentId = ? and
                    userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and
                    isDel = ? and
                    id <= ? order by id desc LIMIT ?`;
            data = [ cardId, '用户卡密用户', 'false', cursor, limit + 1 ];
        } else {
            sql = `select
                    id, card, name, url, count, maxCount, isEnable,
                    DATE_FORMAT(createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User
                where parentId = ? and
                    userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and
                    isDel = ? order by id desc LIMIT ?`;
            data = [ cardId, '用户卡密用户', 'false', limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    // 懒加载待审核用户卡密列表
    getUnenableUserCardLazyLoad(cursor, limit) {
        let sql;
        let data;
        if (cursor) {
            sql = `select
                    u1.id as id,
                    u2.id as subCardId,
                    u1.card as card,
                    u1.name as name,
                    u2.name as subCardName,
                    u1.url as url,
                    u1.count as count,
                    u1.maxCount as maxCount,
                    u1.isEnable as isEnable,
                    DATE_FORMAT(u1.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u1.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u1
                left join User as u2 on u1.parentId = u2.id
                where u1.isEnable = ? and
                    u1.userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and u1.isDel = ? and
                    u1.id <= ? order by u1.id desc LIMIT ?`;
            data = [ 'false', '用户卡密用户', 'false', cursor, limit + 1 ];
        } else {
            sql = `select
                    u1.id as id,
                    u2.id as subCardId,
                    u1.card as card,
                    u1.name as name,
                    u2.name as subCardName,
                    u1.url as url,
                    u1.count as count,
                    u1.maxCount as maxCount,
                    u1.isEnable as isEnable,
                    DATE_FORMAT(u1.createdAt, '%Y-%m-%d %H:%i:%s') as createdAt,
                    DATE_FORMAT(u1.endAt, '%Y-%m-%d %H:%i:%s') as endAt
                from User as u1
                left join User as u2 on u1.parentId = u2.id
                where u1.isEnable = ? and
                    u1.userTypeId = (
                        select id from UserType where name = ? LIMIT 1
                    ) and u1.isDel = ? order by u1.id desc LIMIT ?`;
            data = [ 'false', '用户卡密用户', 'false', limit + 1 ];
        }
        return mysql.sqlExec(sql, data);
    },
    // 审核用户卡密
    enableUserCard(uid, userCardId) {
        let sql = `update User set isEnable = ? where id = ? and isDel = ? and isEnable = ?`;
        let data = [ 'true', userCardId, 'false', 'false' ];
        return mysql.sqlExec(sql, data);
    },
    // 删除用户卡密
    delUserCard(uid, cardId) {
        let sql = `update User set isDel = ? where id = ? and parentId = ? and userTypeId = (
                select id from UserType where name = ?
            )`;
        let data = [ 'true', cardId, uid, '用户卡密用户' ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = managerDao;
